import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONObject;

public class Lianjie {
	
	
	public void insert_data(Product product) {

		// 第一步加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("加载驱动失败。。。。。。");
			e.printStackTrace();
		}


		Connection coon = null;
		try {
			coon = DriverManager.getConnection("jdbc:mysql://rm-wz90qibh708efw9c6co.mysql.rds.aliyuncs.com:3306/iiiup?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "UP626enj!");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("连接数据库失败。。。。。。。");
			e.printStackTrace();
		}
		

		// 第三步创建PrepareStatement对象
		PreparedStatement prep = null;//up_product
		PreparedStatement prep2 = null;//up_product_img
		PreparedStatement prep3 = null;//up_product_description
		PreparedStatement prep4 = null;//up_supplier
		PreparedStatement prep5 = null;//up_product_supplier
		PreparedStatement prep6 = null;//up_product_expand
		Statement stmt = null;
		Statement stmt3 = null;
		Statement stmt4 = null;//up_ezbuy_category_propvalues
		Statement stmt5 = null;//up_product 相同名字
		ResultSet rs =null;
		ResultSet rs1 =null;
		ResultSet rs4 =null;
		ResultSet rs5 =null;//up_ezbuy_category_propvalues
		ResultSet rs6 =null;//up_product 相同名字
		try {
			coon.setAutoCommit(false);
		

			//假如得到的数据不等于0，得到sql有产品数据
			if(!getProductId(product.getSupplierProductid(), coon, rs4, stmt3).equals(0)){
				coon.close();
				return;
			}
			
			if(!getProductName(product.getProductName(), coon, rs6, stmt5).equals(0)){
				System.out.println("有相同产品名称：------------------------"+product.getProductName());
				coon.close();
				return;
			}
			
			
			String up_product="INSERT INTO up_product(product_code,product_name,product_cost,time_add,category_id,product_status,product_cost_base,product_cost_suggest,product_name_sort) VALUES(?,?,?,?,?,?,?,?,?)";
			prep = coon.prepareStatement(up_product, PreparedStatement.RETURN_GENERATED_KEYS);  
			prep.setString(1, RandomNumberGenerator.generateNumber());//随机生成码
			prep.setString(2, product.getProductName());//商品名字
			prep.setFloat(3,  product.getProductCost());//商品进货价
			prep.setInt(4,  product.getTimeAdd());//时间
			prep.setInt(5,  product.getCategoryId());//商品分类 T恤
			prep.setInt(6,  2);
			prep.setFloat(7, product.getProductCost());
			prep.setFloat(8, product.getProductCostSuggest());//淘宝价格
			prep.setString(9, Util.StringFilter(product.getProductName()));
			
			prep.executeUpdate();  
	        //获取由于执行此 Statement 对象而创建的所有自动生成的键。也是继承与java.sql.Statement  
	        rs = prep.getGeneratedKeys();  
	        if (rs.next()) {  
	            System.out.println(rs.getInt(1));  
	            product.setProductId(rs.getInt(1));
	        }  
	        
	        List<ImaUrl> expandDetailsList = product.getExpandDetailsList();
	        for (ImaUrl imaUrl : expandDetailsList) {
				String up_product_img="INSERT INTO up_product_img(product_id,img_url,img_url_small,img_url_big,time_add,img_status,img_language) VALUES(?,?,?,?,?,?,?)";
				prep2 = coon.prepareStatement(up_product_img, PreparedStatement.RETURN_GENERATED_KEYS);  
				prep2.setInt(1, product.getProductId());//产品id
				prep2.setString(2, imaUrl.getImgUrl());//zhong
				prep2.setString(3,  imaUrl.getImgUrlSmall());//xiao
				prep2.setString(4, imaUrl.getImgUrlBig());//da
				prep2.setInt(5,  product.getTimeAdd());//shijian
				prep2.setInt(6,  1);
				prep2.setInt(7,  1);
				prep2.executeUpdate(); 
			}


			String up_product_description="INSERT INTO up_product_description(product_id,desc_source,text_description,time_add,desc_language,desc_status,text_description_concise) VALUES(?,?,?,?,?,?,?)";
			prep3 = coon.prepareStatement(up_product_description, PreparedStatement.RETURN_GENERATED_KEYS);  
			prep3.setInt(1, product.getProductId());//产品id
			prep3.setString(2, "skw");//商品名字
			prep3.setString(3,  product.getTextDescription());//商品描述
			prep3.setInt(4,  product.getTimeAdd());//时间
			prep3.setInt(5,  1);
			prep3.setInt(6,  1);
			prep3.setString(7, product.getTextImgDescription());
			prep3.executeUpdate(); 

			if (getup_supplierId(product.getSupplierName(), coon, rs1, stmt).equals(0)) {
				String up_supplier="INSERT INTO up_supplier(supplier_name,supplier_type,supplier_wangwang,supplier_weixin,supplier_qq,supplier_phone,supplier_address,time_add) "
						+ "VALUES(?,?,?,?,?,?,?,?)";
				prep4 = coon.prepareStatement(up_supplier, PreparedStatement.RETURN_GENERATED_KEYS);  
				prep4.setString(1, product.getSupplierName());//商品名字
				prep4.setInt(2, 1);//类型是搜款网
				if (null==product.getSupplierWangwang()||("").equals(product.getSupplierWangwang())) {
					prep4.setString(3,"ww无数据");//旺旺
				}else{
					prep4.setString(3, product.getSupplierWangwang());//旺旺
				}
				
				if (null==product.getSupplierWeixin()||("").equals(product.getSupplierWeixin())) {
					prep4.setString(4,"wx无数据");//wx
				}else{
					prep4.setString(4, product.getSupplierWeixin());//wx
				}
				
				if (null==product.getSupplierqq()||("").equals(product.getSupplierqq())) {
					prep4.setString(5,"qq无数据");//wx
				}else{
					prep4.setString(5, product.getSupplierqq());//wx
				}
				
				if (null==product.getSupplierPhone()||("").equals(product.getSupplierPhone())) {
					prep4.setString(6,"phone无数据");//phone
				}else{
					prep4.setString(6, product.getSupplierPhone());//phone
				}

//				prep4.setString(5, product.getSupplierqq());//qq
//				prep4.setString(6, product.getSupplierPhone());//phone
				prep4.setString(7, product.getSupplierAddress());//档口地址
				prep4.setInt(8, product.getTimeAdd());
				prep4.executeUpdate(); 
				
				rs1 = prep4.getGeneratedKeys();  
		        if (rs1.next()) {  
		            product.setSupplier_id(rs1.getInt(1));
		        } 
				prep4.close();
			}else{
				product.setSupplier_id(getup_supplierId(product.getSupplierName(), coon, rs1, stmt));
			}
 
	       
	        
			String up_product_supplier="INSERT INTO up_product_supplier(product_id,supplier_productid,supplier_huohao,supplier_id,time_add,product_type) VALUES(?,?,?,?,?,?)";
			prep5 = coon.prepareStatement(up_product_supplier, PreparedStatement.RETURN_GENERATED_KEYS);  
			prep5.setInt(1, product.getProductId());//产品id
			prep5.setString(2, product.getSupplierProductid());//搜款网的商品ID
			prep5.setString(3,  product.getSupplierHuohao());//huohao
			prep5.setInt(4,  product.getSupplier_id());
			prep5.setInt(5, product.getTimeAdd());//时间
			prep5.setInt(6, 1);//原商品
			prep5.executeUpdate(); 
	        

		 List<ImaColor> imaColorList = product.getImaColorList();
   		 if (product.getColour()!=null&&product.getColour().length()>0&&product.getSize()!=null&&product.getSize().length()>0) {
   			String[] Color = product.getColour().split(",");
   			String[] Size = product.getSize().split(",");
   			for (String color : Color) {
   				
   				String skuIma="";
   				//取出sku的图片跟颜色对应的话插入到expand_img
   				for (ImaColor imaColor : imaColorList) {
					if (color.equals(imaColor.getColor())) {
						skuIma=imaColor.getImageUrl();
					}
				}
   				

   				
   				
   				//在颜色的里面循环把图片路径拿出来。
   				String aa = "2";//颜色
   				PreparedStatement prep7 = null;
   				Statement stmt2 = null;
   				ResultSet rs3 =null;
   				int cc =getupProductCategoryAttrValId(color, "2", coon, prep7, rs3, stmt2);
   				
   				//up_ezbuy_category_propvalues
   				getpvalId(color, coon, rs5, stmt4,cc);
   				
   				Map<String, Integer> map = new HashMap<String, Integer>();
   				map.put(aa, cc);
   				for (String size : Size) {
   					String bb = "1";//尺码
   					int dd =getupProductCategoryAttrValId(size, "1", coon, prep7, rs3, stmt2);
   					map.put(bb, dd);
   					String jsonObject = JSONObject.toJSONString(map);
	   					String up_product_expand="INSERT INTO up_product_expand(product_id,expand_details,expand_img) VALUES(?,?,?)";
	   					prep6 = coon.prepareStatement(up_product_expand, PreparedStatement.RETURN_GENERATED_KEYS);  
	   					prep6.setInt(1, product.getProductId());//产品id
	   					prep6.setString(2, jsonObject); 
	   					prep6.setString(3, skuIma);
	   					prep6.executeUpdate(); 
					}
				}
		    
   		 }
   		 	prep.close();
   		 	prep2.close();
   			prep3.close();
   			prep5.close();
   			prep6.close();
	        rs.close();  
			coon.commit();
		} catch (SQLException e) {
			if (coon != null) {
                try {
                	coon.rollback();
                } catch (SQLException e1) {
                    System.out.println(e.toString());
                }
            }
			// TODO Auto-generated catch block
			System.out.println("PreparedStatement 对象创建失败 。。。。");
			e.printStackTrace();
		}finally{
			if (coon != null) {
			}
			try {
 
				coon.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

 
	}
	
	
	//up_ezbuy_category_propvalues
	public void getpvalId(String val_name,Connection coon,ResultSet rs,Statement stmt,Integer valId){
		try {
			PreparedStatement prep8 = null;//up_ezbuy_category_propvalues
			String pval_id = "00";
			Integer id = null;
			stmt = coon.createStatement();
			String sql1 = "select pval_id,id from up_ezbuy_category_propvalues where pid = 122535 AND NAME='"+val_name+"'";
		    rs = stmt.executeQuery(sql1);
		 
		    if(rs.next()) {
		    	pval_id  = rs.getString(1);  
		    	id = rs.getInt(2);
		    }
			if ("0".equals(pval_id)) {
				String upsql = "update up_ezbuy_category_propvalues set pval_id =? where id =?" ;	
				prep8 = coon.prepareStatement(upsql);
				prep8.setInt(1, valId);
				prep8.setInt(2, id);
				prep8.executeUpdate();  
				prep8.close();
			}
			
	        rs.close();
	        stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	    
		return;
	}
	
	
	
	
	public Integer getProductId(String vvicId,Connection coon,ResultSet rs,Statement stmt)throws SQLException {
		
	    int row = 0;
		stmt = coon.createStatement();
		String sql1 = "select product_id from up_product_supplier where supplier_productid = '"+vvicId+"'";
	    rs = stmt.executeQuery(sql1);
	 
	    if(rs.next()) {
	    	row  = rs.getInt(1);  
	    }
        rs.close();
        stmt.close();
	    
		return row;
	}
	
	public Integer getProductName(String proName,Connection coon,ResultSet rs,Statement stmt)throws SQLException {
		
	    int row = 0;
		stmt = coon.createStatement();
		String sql1 = "select product_id from up_product where product_name = '"+proName+"'";
	    rs = stmt.executeQuery(sql1);
	 
	    if(rs.next()) {
	    	row  = rs.getInt(1);  
	    }
        rs.close();
        stmt.close();
	    
		return row;
	}
	
	
	//得到valid  查询up_product_category_attr_val没有颜色或者尺码就插入一条
	public Integer  getupProductCategoryAttrValId(String val_name,String attr_id ,Connection coon,PreparedStatement prep,ResultSet rs,Statement stmt) throws SQLException {
 
			stmt = coon.createStatement();
		    String sql1 = "SELECT  val_id FROM up_product_category_attr_val where val_name = '"+val_name+"'";
		    rs = stmt.executeQuery(sql1);
	    if(rs.next()) {
	        int row = rs.getInt(1);  
	        return row;
		}else{
		  	String up_product_category_attr_val="INSERT INTO up_product_category_attr_val(attr_id,val_name) VALUES(?,?)";
			prep = coon.prepareStatement(up_product_category_attr_val, PreparedStatement.RETURN_GENERATED_KEYS);  
			prep.setInt(1, Integer.decode(attr_id));
			prep.setString(2, val_name); 
			prep.executeUpdate();  
	        //获取由于执行此 Statement 对象而创建的所有自动生成的键。也是继承与java.sql.Statement  
	        rs = prep.getGeneratedKeys();  
	        if (rs.next()) {  
	            return rs.getInt(1);
	        }  
	        prep.close();
		}
        rs.close();
        stmt.close();
        return  111;
	}
	
	//得到valid
	public Integer  getup_supplierId(String suppname,Connection coon,ResultSet rs,Statement stmt) throws SQLException {
		stmt = coon.createStatement();
	    String sql1 = "SELECT  supplier_id FROM up_supplier where supplier_name = '"+suppname+"'";
	    rs = stmt.executeQuery(sql1);
	    if(rs.next()) {
	        int row = rs.getInt(1);
	        rs.close();
	        stmt.close();
	        return row;
		}
        
        rs.close();
        stmt.close();
        return  0;
	}
	
}